﻿Public Class QueryBuilder


    Dim Variable() As String
    Dim Value() As String
    Dim Type() As String
    Dim Counter As Integer = 0

    Sub AddField(ByVal FieldNameIn, ByVal ValueIn, ByVal TypeIn, Optional ByVal Joiner = "AND")
        Counter = Counter + 1
        ReDim Preserve Variable(Counter)
        ReDim Preserve Value(Counter)
        ReDim Preserve Type(Counter)
        Variable(Counter - 1) = FieldNameIn

        Type(Counter - 1) = TypeIn

        If Not ValueIn.ToString = SkipField Then
            Dim Valid As Boolean = True
            Select Case LCase(TypeIn)
                Case "string"
                    'this will prevent users getting access to sql injection
                    ValueIn = "'" & Replace(ValueIn, "'", "''") & "'"
                Case "integer"
                    If Not IsNumeric(ValueIn) Then
                        Valid = False
                    End If
                Case "ntext"
                    ValueIn = "'" & Replace(ValueIn, "'", "''") & "'"
                Case "date"
                    ValueIn = "'" & ValueIn & "'"
                    'need to work on a way to validate dates with different localization             
                Case Else
                    Throw New Exception("Invalid Type (" & TypeIn & ") specified for field " & FieldNameIn)
            End Select

            If Valid = False Then
                Throw New Exception("Invalid Data Type, text for field " & FieldNameIn & " is not a " & TypeIn)
            Else
                Value(Counter - 1) = ValueIn
            End If
        Else
            Value(Counter - 1) = ValueIn
        End If



    End Sub

    Function GetInsertString(ByVal TableName) As String
        Dim SQL As String
        SQL = "INSERT INTO [" & TableName & "] ("

        Dim RecordsFound As Boolean = False
        Dim x As Integer

        For x = 0 To Counter - 1
            If Not Value(x).ToString = SkipField Then
                If Len(Value(x).ToString) > 0 Then
                    If RecordsFound Then
                        SQL = SQL & "," & Variable(x)
                    Else
                        RecordsFound = True
                        SQL = SQL & Variable(x)
                    End If
                End If
            End If
        Next

        SQL = SQL & ") VALUES ("

        'now set values
        RecordsFound = False
        x = 0
        For x = 0 To Counter - 1
            If Not Value(x).ToString = SkipField Then
                If Len(Value(x).ToString) > 0 Then
                    If RecordsFound Then
                        SQL = SQL & "," & Value(x)
                    Else
                        RecordsFound = True
                        SQL = SQL & Value(x)
                    End If

                    Select Case Type(x)

                    End Select

                End If
            End If
        Next
        SQL = SQL & ")"

        If RecordsFound Then
            Return SQL
        Else
            Return ""
        End If


    End Function

    Function GetUpdateString(ByVal TableName, ByVal WhereClause)

        Dim SQL As String
        SQL = "UPDATE [" & TableName & "] SET "

        Dim RecordsFound As Boolean = False
        Dim x As Integer

        For x = 0 To Counter - 1
            If Not Value(x).ToString = SkipField Then
                If Len(Value(x).ToString) > 0 Then
                    If RecordsFound Then
                        SQL = SQL & "," & Variable(x) & "=" & Value(x)
                    Else
                        RecordsFound = True
                        SQL = SQL & Variable(x) & "=" & Value(x)
                    End If
                End If
            End If
        Next

        SQL = SQL & "" & WhereClause

        If RecordsFound Then
            Return SQL
        Else
            Return ""
        End If
    End Function


    Function GetSearchString(ByVal Tablename, Optional ByVal FieldNames = "*") As String
        Dim SQL As String
        SQL = "SELECT " & FieldNames & " FROM [" & Tablename & "] WHERE "

        Dim RecordsFound As Boolean = False
        Dim x As Integer

        For x = 0 To Counter - 1
            If Not Value(x).ToString = SkipField Then
                If Len(Value(x).ToString) > 0 Then
                    If RecordsFound Then
                        SQL = SQL & "," & Variable(x) & "=" & Value(x)
                    Else
                        RecordsFound = True
                        SQL = SQL & Variable(x) & "=" & Value(x)
                    End If
                End If
            End If
        Next

        If RecordsFound Then
            Return SQL
        Else
            Return ""
        End If

    End Function

End Class
